﻿CREATE FUNCTION [dbo].[RelativeGoods]
(
	@GoodsId int
)
RETURNS @tblAllRelatives TABLE (
	[Id] [int] NOT NULL
	PRIMARY KEY (Id)
)
AS
BEGIN
/* Все товары родственные GoodsId*/
DECLARE @intRecCount int

DECLARE @tblNewRecords TABLE (
	[Id] [int] NOT NULL
	PRIMARY KEY (Id)
)

DECLARE @tblSelectedRecords TABLE (
	[Id] [int] NOT NULL
	PRIMARY KEY (Id)
)

SELECT @intRecCount = 1

INSERT INTO @tblNewRecords (Id) VALUES (@GoodsId)

WHILE @intRecCount > 0
BEGIN
	DELETE FROM @tblSelectedRecords

	INSERT INTO @tblSelectedRecords (Id)
	SELECT P.Id AS Id --родители
	FROM [CA].[Goods] P INNER JOIN @tblNewRecords S ON P.ChildId = S.ID
	UNION
	SELECT C.Id AS Id --потомки
	FROM @tblNewRecords S INNER JOIN [CA].[Goods] P ON S.Id = P.Id
  INNER JOIN [CA].[Goods] C ON P.ChildId = C.ID

	IF @@rowcount = 0 BREAK

	DELETE FROM @tblNewRecords

	INSERT INTO @tblAllRelatives (Id)
	OUTPUT INSERTED.Id INTO @tblNewRecords (Id)
	SELECT S.Id FROM @tblSelectedRecords S LEFT OUTER JOIN @tblAllRelatives D
	ON S.Id = D.Id WHERE D.Id Is Null AND S.Id <> @GoodsId --добавляем только тех родственников, которых еще не добавили

	SELECT @intRecCount = @@rowcount
END

	RETURN
END
